﻿using System;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.WebControls;
using Business;
using EntitiesTM;
using System.Threading;
using System.Data;
using System.Text;
using System.IO;

namespace TimeWork.report.general
{
    public partial class Cumulative : System.Web.UI.Page
    {
        private void BindDdlProject()
        {
            Dictionary<string, string> data = BuProject.selProjectByParentIDTreeReport(0, Session["companyid"].ToString());
            ListItem item;
            item = new ListItem("Tất cả sản phẩm", "0");
            ddlproject.Items.Add(item);

            foreach (KeyValuePair<string, string> pair in data)
            {
                item = new ListItem(pair.Value, pair.Key);
                ddlproject.Items.Add(item);
            }
        }

        protected void ddlproject_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (!ddlproject.SelectedValue.Equals("0"))
            {
                int projectLevel = BuProject.selProjectByIDTree(Int32.Parse(ddlproject.SelectedValue), Session["companyid"].ToString()).projectLevel;
                if (projectLevel == 3)
                {
                    // Cho hien DropDownList activity va do du lieu
                    ddlActivity.Visible = true;
                    EnProject project = new EnProject();
                    project.projectID = Int32.Parse(ddlproject.SelectedValue);
                    ddlActivity.DataSource = BuProject.selActivityByProjectExceptID(project, Session["companyid"].ToString());
                    ddlActivity.DataTextField = "ActivityName";
                    ddlActivity.DataValueField = "ActivityID";
                    ddlActivity.DataBind();
                    ddlActivity.Items.Insert(0, new ListItem("Tất cả công việc", "0"));
                    ddlActivity.SelectedValue = "0";
                }
                else
                {
                    ddlActivity.Visible = false;
                }
                string value = ddlActivity.SelectedValue;
            }
            else
            {
                ddlActivity.Visible = false;
            }
        }
        
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["email"] == null)
            {
                Response.Redirect("http://"+Request.Url.Authority+"/Login.aspx");
            }
            if (!IsPostBack)
            {
                BindDdlProject();
                ddlActivity.Visible = false;
            }
        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            return;
        }
        protected void btnExportExcel_Click(object sender, ImageClickEventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AppendHeader("Content-Disposition", "attachment;filename=BaoCaoThoiGianCongDon" + DateTime.
            Now.ToString("yyyyMMdd") + ".xls");
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/ms-excel";
            this.EnableViewState = false;

            var sb = new StringBuilder();
            ltrData.RenderControl(new HtmlTextWriter(new StringWriter(sb)));
            Response.Write(sb.ToString());
            Response.End();
        }

        private string genSqlStringUnit()
        {
            string companyid = Session["companyid"].ToString();
            string result = String.Empty;
            try
            {
                // Lay thang va nam tu dropdownlist
                int month = Int32.Parse(ddlMonth.SelectedValue);
                int year = Int32.Parse(ddlYear.SelectedValue);

                result += "SELECT r.ResultUnitID, r.ResultUnitName, r.ResultUnitNameAlt, r.ResultUnitOrder,";
                result += "ISNULL(SUM(CASE WHEN DATEPART(M,SADate) = " + month + " AND DATEPART(YY,SADate) = " + year + " THEN SAReportTimeHour END),0) FirstTimeH,";
                result += "ISNULL(SUM(CASE WHEN DATEPART(M,SADate) = " + month + " AND DATEPART(YY,SADate) = " + year + " THEN SAReportTimeMinute END),0) FirstTimeM,";
                result += "ISNULL(SUM(CASE WHEN DATEPART(M,SADate) >= 1 AND DATEPART(M,SADate) <= " + month + " AND DATEPART(YY,SADate) = " + year + " THEN SAReportTimeHour END),0) SecondTimeH,";
                result += "ISNULL(SUM(CASE WHEN DATEPART(M,SADate) >= 1 AND DATEPART(M,SADate) <= " + month + " AND DATEPART(YY,SADate) = " + year + " THEN SAReportTimeMinute END),0) SecondTimeM ";
                result += "FROM VTK_ResultUnit"+companyid+" as r FULL JOIN VTK_Staff"+companyid+" as s ON r.ResultUnitID = s.StaffResultUnitID ";
                result += "FULL JOIN (SELECT SAStaffID,SADate, SAReportTimeHour, SAReportTimeMinute, SATypeTimeID, SAActivityID FROM VTK_SA"+companyid+" ";

                // Lay id cua project
                int projectID = Int32.Parse(ddlproject.SelectedValue);
                // Neu projectID khac 0, truy van toan bo bang theo datetime

                if (projectID != 0)
                {
                    // Neu project != 0 thi phai them dieu kien
                    int projectLevel = BuProject.selProjectByIDTree(Int32.Parse(ddlproject.SelectedValue), Session["companyid"].ToString()).projectLevel;
                    // Truong hop level = 3 thi xuat hien lua chon cong viec
                    if (projectLevel == 3)
                    {
                        // Truong hop chon tat ca
                        if (ddlActivity.SelectedValue.Equals("0"))
                        {
                            result += "WHERE SAActivityID IN (SELECT ActivityID FROM VTK_Activity"+companyid+" WHERE ActivityProjectID = " + projectID + ")";
                        }
                        // Neu chi chon lua 01 activity
                        else
                        {
                            result += "WHERE SAActivityID = " + ddlActivity.SelectedValue;
                        }
                    }
                    if (projectLevel == 2)
                    {
                        result += "WHERE SAActivityID IN (SELECT ActivityID FROM VTK_Activity"+companyid+" WHERE ActivityProjectID IN (SELECT ProjectID FROM VTK_Project"+companyid+" WHERE ProjectParentID = " + projectID + "))";
                    }
                    if (projectLevel == 1)
                    {
                        result += "WHERE SAActivityID IN (SELECT ActivityID FROM VTK_Activity"+companyid+" WHERE ActivityProjectID IN (SELECT ProjectID FROM VTK_Project"+companyid+" WHERE ProjectParentID IN (SELECT ProjectID FROM VTK_Project"+companyid+" WHERE ProjectParentID = " + projectID + ")))";
                    }
                }

                result += ") as sa ON sa.SAStaffID = s.StaffID FULL JOIN VTK_TypeTime"+companyid+" as t ON sa.SATypeTimeID = t.TypeTimeID ";
                result += "GROUP BY r.ResultUnitID, r.ResultUnitName, r.ResultUnitNameAlt, r.ResultUnitOrder ORDER BY ResultUnitOrder";
            }
            catch { }
            return result;
        }

        private string genSqlStringUser(string resultUnitID)
        {
            string companyid = Session["companyid"].ToString();
            string result = String.Empty;
            try
            {
                int month = Int32.Parse(ddlMonth.SelectedValue);
                int year = Int32.Parse(ddlYear.SelectedValue);

                result += "SELECT s.StaffID,s.StaffName, s.StaffScale, s.StaffLevel,";
                result += "ISNULL(SUM(CASE WHEN DATEPART(M,SADate) = " + month + " AND DATEPART(YY,SADate) = " + year + " THEN SAReportTimeHour END),0) FirstTimeH,";
                result += "ISNULL(SUM(CASE WHEN DATEPART(M,SADate) = " + month + " AND DATEPART(YY,SADate) = " + year + " THEN SAReportTimeMinute END),0) FirstTimeM,";
                result += "ISNULL(SUM(CASE WHEN DATEPART(M,SADate) >= 1 AND DATEPART(M,SADate) <= " + month + " AND DATEPART(YY,SADate) = " + year + " THEN SAReportTimeHour END),0) SecondTimeH,";
                result += "ISNULL(SUM(CASE WHEN DATEPART(M,SADate) >= 1 AND DATEPART(M,SADate) <= " + month + " AND DATEPART(YY,SADate) = " + year + " THEN SAReportTimeMinute END),0) SecondTimeM ";
                result += "FROM VTK_ResultUnit" + companyid + " as r FULL JOIN VTK_Staff" + companyid + " as s ON r.ResultUnitID = s.StaffResultUnitID ";
                result += "FULL JOIN (SELECT SAStaffID,SADate, SAReportTimeHour, SAReportTimeMinute, SATypeTimeID, SAActivityID FROM VTK_SA" + companyid + " ";
                // Lay id cua project
                int projectID = Int32.Parse(ddlproject.SelectedValue);
                // Neu projectID khac 0, truy van toan bo bang theo datetime

                if (projectID != 0)
                {
                    // Neu project != 0 thi phai them dieu kien
                    int projectLevel = BuProject.selProjectByIDTree(Int32.Parse(ddlproject.SelectedValue), Session["companyid"].ToString()).projectLevel;
                    // Truong hop level = 3 thi xuat hien lua chon cong viec
                    if (projectLevel == 3)
                    {
                        // Truong hop chon tat ca
                        if (ddlActivity.SelectedValue.Equals("0"))
                        {
                            result += "WHERE SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID = " + projectID + ")";
                        }
                        // Neu chi chon lua 01 activity
                        else
                        {
                            result += "WHERE SAActivityID = " + ddlActivity.SelectedValue;
                        }
                    }
                    if (projectLevel == 2)
                    {
                        result += "WHERE SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID = " + projectID + "))";
                    }
                    if (projectLevel == 1)
                    {
                        result += "WHERE SAActivityID IN (SELECT ActivityID FROM VTK_Activity" + companyid + " WHERE ActivityProjectID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID IN (SELECT ProjectID FROM VTK_Project" + companyid + " WHERE ProjectParentID = " + projectID + ")))";
                    }
                }

                result += ") as sa ON sa.SAStaffID = s.StaffID FULL JOIN VTK_TypeTime" + companyid + " as t ON sa.SATypeTimeID = t.TypeTimeID ";
                result += "WHERE s.StaffResultUnitID = " + resultUnitID + " ";
                result += "GROUP BY s.StaffID,s.StaffName, s.StaffScale, s.StaffLevel ";
                result += "ORDER BY s.StaffName";

            }
            catch { }
            return result;
        }

        protected void btnGenerate_Click(object sender, EventArgs e)
        {
            ltrData.Text = String.Empty;
            Thread.Sleep(500);
            string test = genSqlStringUnit();
            DataTable dataUnit = BuReport.selReportDetailedOri(genSqlStringUnit());
            string tmp = @"<table><tr>
			               <th align='center' style='color:#375B91;background-color:#F3F9FF;width:29px;border: 1px solid #D9E6F0;'>TT</th>
                           <th align='center' style='color:#375B91;background-color:#F3F9FF;width:240px;border: 1px solid #D9E6F0;'>Đơn vị/Cá nhân</th>
                           <th align='center' style='color:#375B91;background-color:#F3F9FF;width:60px;border: 1px solid #D9E6F0;'>Mã đơn vị/Công chức</th>
                           <th align='center' style='color:#375B91;background-color:#F3F9FF;width:80px;border: 1px solid #D9E6F0;'>Tổng số thời gian sử dụng tháng</th>
                           <th align='center' style='color:#375B91;background-color:#F3F9FF;width:80px;border: 1px solid #D9E6F0;'>Thời gian sử dụng trong năm</th></tr>";


            int hour, minute, addHour;
            // Bien luu tru tong tam thoi
            int sumH1 = 0, sumM1 = 0, sumH2 = 0, sumM2 = 0;
            int count = 1;
            string dataString = String.Empty;
            foreach (DataRow item in dataUnit.Rows)
            {
                if (!item["ResultUnitID"].ToString().Equals(""))
                {
                    // Thoi gian su dung thang
                    hour = Int32.Parse(item["FirstTimeH"].ToString());
                    minute = Int32.Parse(item["FirstTimeM"].ToString());
                    sumH1 += hour;
                    sumM1 += minute;
                    if (minute >= 60)
                    {
                        addHour = minute / 60;
                        minute -= 60 * addHour;
                        hour += addHour;
                    }
                    dataString += "<tr><td align='center' style='border: 1px solid #D9E6F0;'>" + count + "</td><td style='border: 1px solid #D9E6F0;'>" + item["ResultUnitName"].ToString() + "</td><td align='center' style='border: 1px solid #D9E6F0;'>" + item["ResultUnitNameAlt"].ToString() + "</td><td align='center' style='border: 1px solid #D9E6F0;'>" + hour + "," + minute + "</td>";
                    // Thoi gian su dung nam
                    hour = Int32.Parse(item["SecondTimeH"].ToString());
                    minute = Int32.Parse(item["SecondTimeM"].ToString());
                    sumH2 += hour;
                    sumM2 += minute;
                    if (minute >= 60)
                    {
                        addHour = minute / 60;
                        minute -= 60 * addHour;
                        hour += addHour;
                    }
                    dataString += "<td align='center' style='border: 1px solid #D9E6F0;'>" + hour + "," + minute + "</td></tr>";
                    
                    count++;
                }
            }

            tmp += "<tr><td style='border: 1px solid #D9E6F0;'>&nbsp</td><td style='border: 1px solid #D9E6F0;'><center><strong>Tổng số</strong></center></td><td style='border: 1px solid #D9E6F0;'>&nbsp</td>";

            // Thoi gian su dung thang
            if (sumM1 >= 60)
            {
                addHour = sumM1 / 60;
                sumM1 -= 60 * addHour;
                sumH1 += addHour;
            }
            tmp += "<td align='center' style='border: 1px solid #D9E6F0;'>" + sumH1 + "," + sumM1 + "</td>";
            // Thoi gian su dung nam
            if (sumM2 >= 60)
            {
                addHour = sumM2 / 60;
                sumM2 -= 60 * addHour;
                sumH2 += addHour;
            }
            tmp += "<td align='center' style='border: 1px solid #D9E6F0;'>" + sumH2 + "," + sumM2 + "</td></tr>";

            tmp += "<tr><td align='center' style='border: 1px solid #D9E6F0;'><strong>I</strong></td><td><strong>Theo đơn vị</strong></td><td style='border: 1px solid #D9E6F0;'>&nbsp</td><td style='border: 1px solid #D9E6F0;'>&nbsp</td><td style='border: 1px solid #D9E6F0;'>&nbsp</td></tr>";
            tmp += dataString;

            // Bao cao theo ca nhan
            tmp += "<tr><td align='center' style='border: 1px solid #D9E6F0;'><strong>II</strong></td><td style='border: 1px solid #D9E6F0;'><strong>Theo cá nhân</strong></td><td style='border: 1px solid #D9E6F0;'>&nbsp</td><td style='border: 1px solid #D9E6F0;'>&nbsp</td><td style='border: 1px solid #D9E6F0;'>&nbsp</td></tr>";


            DataTable dataUser;
            foreach (DataRow item in dataUnit.Rows)
            {
                count = 1;
                if (!item["ResultUnitID"].ToString().Equals(""))
                {
                    tmp += "<tr><td align='center' style='border: 1px solid #D9E6F0;'>&nbsp;</td><td style='border: 1px solid #D9E6F0;'><strong><i>" + item["ResultUnitName"].ToString() + "</i></strong></td><td style='border: 1px solid #D9E6F0;'>&nbsp</td><td style='border: 1px solid #D9E6F0;'>&nbsp</td><td style='border: 1px solid #D9E6F0;'>&nbsp</td></tr>";
                    dataUser = BuReport.selReportDetailedOri(genSqlStringUser(item["ResultUnitID"].ToString()));
                    foreach (DataRow user in dataUser.Rows)
                    {
                        

                        // Thoi gian su dung thang
                        hour = Int32.Parse(user["FirstTimeH"].ToString());
                        minute = Int32.Parse(user["FirstTimeM"].ToString());

                        if (minute >= 60)
                        {
                            addHour = minute / 60;
                            minute -= 60 * addHour;
                            hour += addHour;
                        }
                        tmp += "<tr><td align='center' style='border: 1px solid #D9E6F0;'>" + count + "</td><td style='border: 1px solid #D9E6F0;'>" + user["StaffName"].ToString() + "</td><td align='center' style='border: 1px solid #D9E6F0;'>" + user["StaffScale"].ToString() + "</td><td align='center' style='border: 1px solid #D9E6F0;'>" + hour + "," + minute + "</td>";
                        // Thoi gian su dung nam
                        hour = Int32.Parse(user["SecondTimeH"].ToString());
                        minute = Int32.Parse(user["SecondTimeM"].ToString());

                        if (minute >= 60)
                        {
                            addHour = minute / 60;
                            minute -= 60 * addHour;
                            hour += addHour;
                        }
                        tmp += "<td align='center' style='border: 1px solid #D9E6F0;'>" + hour + "," + minute + "</td></tr>";
                        
                        count++;
                    }
                }
            }
            tmp += "</table>";
            ltrData.Text = tmp;
        }

    }
}